-- @owner: Alice
-- @date: 2025-09-03
-- @testpoint: 确定性函数支持result_cache,带有游标的函数，合理报错
@conn PrimaryDbAmode;
drop extension if exists gms_output;
drop function if exists test_record_cursor;
create extension gms_output;
--
set enable_func_cache = 1;
select gms_output.enable();
set query_dop=1;


set behavior_compat_options='skip_insert_gs_source,select_into_return_null,proc_outparam_override';
--创建表
drop table if exists emp_rec;
CREATE TABLE emp_rec
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
INSERT INTO emp_rec VALUES (7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO emp_rec VALUES (7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO emp_rec VALUES (7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO emp_rec VALUES (7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO emp_rec VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO emp_rec VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO emp_rec VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO emp_rec VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO emp_rec VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO emp_rec VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO emp_rec VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO emp_rec VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO emp_rec VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO emp_rec VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
--创建函数
create or replace function test_record_cursor(p_w VARCHAR2)
RETURNS VARCHAR2 stable result_cache AS $$
DECLARE
type record is record (mgr int not null =10,comm varchar2 not null = 'abc');
CURSOR C1 IS SELECT mgr, comm FROM emp_rec WHERE comm is not null order by nvl(mgr,0) limit 1;
--CURSOR C1 IS SELECT distinct mgr, comm FROM emp_rec WHERE comm is not null order by nvl(mgr,0) limit 1;
employer record;
BEGIN
raise notice 'NOTICE: excute function...';
OPEN C1;
FETCH C1 INTO employer;
raise info 'employer comm: % , mgr: %', employer.comm, employer.mgr;
CLOSE C1;
RETURN p_w;
END;
$$ LANGUAGE plpgsql;
/
--调用函数
select test_record_cursor('abc');
select test_record_cursor(job) from emp_rec;
--case21
--创建表及函数
drop table if exists all_support_type;
create table all_support_type(
id int,
col_bool bool,
col_smallint smallint,
col_int int,
col_bigint bigint,
col_float float,
col_double_precision double precision,
col_char char(820),
col_varchar varchar(920),
col_varchar2 varchar2(20),
col_timestamp timestamp,
col_numeric numeric,
col_number number,
col_text text
);
insert into all_support_type values( 1, 'f',1,1,1,1,1,'1_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values( 2, 'f',2,2,2,2,2,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 3, 'f',0,0,0,0,0,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values( 4, 'f',1,1,1,1,1,'1_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values( 5, 'f',2,2,2,2,2,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 6, 'f',0,0,0,0,0,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',0,0,'1_haha');
insert into all_support_type values( 7, 'f',1,1,1,1,1,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values( 8, 'f',2,2,2,2,2,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 9, 'f',0,0,0,0,0,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(10, 'f',1,1,1,1,1,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values(11, 'f',2,2,2,2,2,'0_haha','0_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values(12, 'f',0,0,0,0,0,'0_haha','0_haha','0_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(13, 'f',1,1,1,1,1,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values(14, 'f',2,2,2,2,2,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',2,2,'1_haha');
insert into all_support_type values(15, 'f',0,0,0,0,0,'1_haha','0_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(16, 'f',1,1,1,1,1,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values(17, 'f',2,2,2,2,2,'1_haha','0_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values(18, 'f',0,0,0,0,0,'1_haha','1_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(19, 'f',1,1,1,1,1,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values(20, 'f',2,2,2,2,2,'1_haha','1_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 1, 'f',1,1,1,1,1,'1_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values( 2, 'f',2,2,2,2,2,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 3, 'f',0,0,0,0,0,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values( 4, 'f',1,1,1,1,1,'1_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values( 5, 'f',2,2,2,2,2,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 6, 'f',0,0,0,0,0,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',0,0,'1_haha');
insert into all_support_type values( 7, 'f',1,1,1,1,1,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values( 8, 'f',2,2,2,2,2,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 9, 'f',0,0,0,0,0,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(10, 'f',1,1,1,1,1,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values(11, 'f',2,2,2,2,2,'0_haha','0_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values(12, 'f',0,0,0,0,0,'0_haha','0_haha','0_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(13, 'f',1,1,1,1,1,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values(14, 'f',2,2,2,2,2,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',2,2,'1_haha');
insert into all_support_type values(15, 'f',0,0,0,0,0,'1_haha','0_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(16, 'f',1,1,1,1,1,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values(17, 'f',2,2,2,2,2,'1_haha','0_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values(18, 'f',0,0,0,0,0,'1_haha','1_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(19, 'f',1,1,1,1,1,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values(20, 'f',2,2,2,2,2,'1_haha','1_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
select count(*) from (select * from all_support_type order by id);
create or replace function func_smallint_cache(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
--缓存命中率小于15%
select func_smallint_cache(id) from (select * from all_support_type order by id);
--teardown
drop function if exists test_record_cursor;
drop function if exists func_smallint_cache;
